ALTER PROCEDURE dbo.ctlg_GetEmptyCategories
(
	@CatalogName    nvarchar(85), 
	@EnableInventory bit = 0,
	@InventoryServerName sysname= null,
	@InventoryDatabaseName sysname = null,
	@FilterOutOfStockProducts bit = 0,
	@FilterBackOrderableProducts bit  = 0, 
	@FilterPreOrderableProducts bit  = 0,
	@UseThresholdAsFloor bit = 0,
	@TreatmissingAsOutOfStock bit = 0,
	@StockHandling int = 0
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Query_tmp nvarchar(4000)
	DECLARE @Step_tmp int
	DECLARE @InventoryCondition_tmp nvarchar(4000) 
	DECLARE @Inventorycatalogjoincondition_tmp nvarchar(1000)
	DECLARE @fVirtualCatalog bit
	
	CREATE TABLE #Categories_list 
	(
		CategoryName nvarchar(128),
		CatalogName nvarchar(85) ,
		Oid int ,
		IsEmpty bit
	)

	SELECT @fVirtualCatalog=IsVirtualCatalog from dbo.CatalogGlobal where CatalogName = @CatalogName
	 
	--Get the list of all categories in the catalog
	SET @Query_tmp = 'INSERT INTO #Categories_list(CategoryName, CatalogName, Oid, IsEmpty) ' + 
	' SELECT CategoryName, BaseCatalogName, oid, 1 from ' + 
	dbo.fn_GetCatalogObjectName(N'CLV',@CatalogName,NULL) + ' where CategoryName IS NOT NULL'
	EXEC sp_executesql @Query_tmp,N'@CatalogName nvarchar(85)',@CatalogName = @CatalogName

	--Get the categories that do not have any products under it directly

	IF @EnableInventory = 1
	BEGIN
		EXEC dbo.inv_GetInventoryClause @InventoryServerName,@InventoryDatabaseName,@UseThresholdAsFloor,@StockHandling,
		@CatalogName,NULL,  @fVirtualCatalog,12,@TreatmissingAsOutOfStock,@FilterOutOfStockProducts,@FilterBackOrderableProducts, 
		@FilterPreOrderableProducts, 
		@InventoryCondition_tmp OUT, @Inventorycatalogjoincondition_tmp OUT
		
		SET @Query_tmp =  N'UPDATE  #Categories_list SET IsEmpty = 0 FROM 
			 (SELECT C.oid, C.CatalogName From 
				(SELECT A.child_oid, A.Child_CatalogName From 
					(SELECT H.child_oid, H.child_CatalogName from ' +
						dbo.fn_GetCatalogObjectName(N'CHT',@CatalogName,NULL) + ' H JOIN ' +
						dbo.fn_GetCatalogObjectName(N'CLV',@CatalogName,NULL) + ' P 
						ON P.oid = H.child_oid AND H.child_CatalogName = P.BaseCatalogName 
						Where P.i_ClassType in (4,8)
					) AS A JOIN 
					(' + @InventoryCondition_tmp +') AS F 
					ON A.child_oid =  F.oid AND A.child_CatalogName =  F.BaseCatalogName 
				) AS B JOIN ' +
				dbo.fn_GetCatalogObjectName(N'CHT',@CatalogName,NULL)+ ' C 
				ON C.child_oid = B.child_oid AND C.Child_CatalogName = B.Child_CatalogName 
				Group by C.oid, C.CatalogName having Count(''*'') > 0
			) AS D JOIN 
			#Categories_list E ON D.oid = E.oid AND D.CatalogName = E.CatalogName'

	END
	ELSE
	BEGIN

		SET @Query_tmp =  N'UPDATE  #Categories_list SET IsEmpty = 0 FROM (SELECT H.oid,H.CatalogName from ' +
		dbo.fn_GetCatalogObjectName(N'CHT',@CatalogName,NULL) + ' H JOIN ' +
		dbo.fn_GetCatalogObjectName(N'CLV',@CatalogName,NULL) + ' P ON P.oid = H.child_oid AND
		 H.child_CatalogName = P.BaseCatalogName Where 
		P.i_ClassType in (4,8)  ' +
		'Group by H.oid,H.CatalogName having Count(''*'') > 0 )  AS A JOIN #Categories_list B ON A.oid = B.oid 
		AND A.CatalogName = B.CatalogName'
	END

	EXEC sp_executesql @Query_tmp,N'@CatalogName nvarchar(85)',@CatalogName = @CatalogName

	CREATE TABLE #Ancestors_list
	(
		CatalogName nvarchar(85),
		oid int,
		step smallint,
		PRIMARY KEY CLUSTERED (Oid, CatalogName)
	)

	--TRAVERSING TO GET THE LIST OF ANCESTOR CATEGORIES FOR NON_EMPTY CATEGORIES (Need to add duplicate check!)
	SET @Query_tmp = N'INSERT INTO #Ancestors_list (CatalogName, oid, step)
		SELECT DISTINCT H.CatalogName, H.oid, 0 FROM '+ dbo.fn_GetCatalogObjectName(N'CHT',@CatalogName,NULL) +
		N' H WHERE H.child_oid IN (Select oid FROM #Categories_list L where 
		IsEmpty = 0 AND H.Child_CatalogName = CatalogName )'

	EXEC sp_executesql @Query_tmp,N'@CatalogName nvarchar(85)',@CatalogName = @CatalogName

	IF @@rowcount = 0 
	BEGIN
		SET @Query_tmp = N'SELECT C.* FROM '+dbo.fn_GetCatalogObjectName(N'CLV',@CatalogName,NULL)+
			N' C, #Categories_list T WHERE C.oid = T.oid AND C.BaseCatalogName = T.CatalogName
			AND T.IsEmpty = 1'	
		EXEC sp_executesql @Query_tmp
		RETURN	
	END
	
	SET @step_tmp = 0
	WHILE (1 = 1 )
	BEGIN
		SET @Query_tmp = N'INSERT INTO #Ancestors_list (CatalogName, Oid, Step)
		SELECT  DISTINCT A.CatalogName, A.oid, @step_tmp+1
		FROM '+ dbo.fn_GetCatalogObjectName(N'CHT',@CatalogName,NULL)+ 
		N' A JOIN #Ancestors_list B ON B.oid = A.child_oid Where B.CatalogName = A.child_CatalogName 
		AND B.step = @step_tmp 
		AND A.oid not in (select oid from #Ancestors_list WHERE CatalogName = A.CatalogName
		UNION select oid FROM #Categories_list where IsEmpty = 0 and CatalogName = A.CatalogName)'
		EXEC sp_executesql @Query_tmp,N'@step_tmp int, @CatalogName nvarchar(85)', @step_tmp, @CatalogName
		IF (@@rowcount = 0)
				BREAK
		SET @step_tmp = @step_tmp + 1
	END

	UPDATE #Categories_list SET IsEmpty = 0 FROM (Select Oid,CatalogName from #Ancestors_list) AS A JOIN
		#Categories_list B ON A.Oid = B.OId AND A.CatalogName = B.CatalogName

	SET @Query_tmp = N'SELECT C.* FROM '+dbo.fn_GetCatalogObjectName(N'CLV',@CatalogName,NULL)+
		N' C, #Categories_list T WHERE C.oid = T.oid AND C.BaseCatalogName = T.CatalogName
		AND T.IsEmpty = 1'
	EXEC sp_executesql @Query_tmp
	
	DROP TABLE #Ancestors_list
	DROP TABLE #Categories_list
END
GO